//
//  DataAcess.m
//  Prospectus
//
//  Created by Ken Power on 04/07/2011.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "DataAccess.h"

// Reference to the SQLite database. 

static sqlite3* database;

@implementation DataAccess



// Open the database connection 
+ (void)initializeDatabase {
	// Get the database from the application bundle. 
	NSString *path=  [[NSBundle mainBundle] pathForResource:@"prospectus" ofType:@"db"];
	
	// Open the database. 
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
		NSLog(@"Opening Database");
	} else {
		// Call close to properly clean up 
		sqlite3_close(database); 
		NSAssert1(0, @"Failed to open database: ‘%s’.",sqlite3_errmsg(database));
	}
}

+(void) closeDatabase {
	// Close the database. 
	if (sqlite3_close(database) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to close database: ‘%s’.", sqlite3_errmsg(database));
	}
}

+ (NSArray*) getAllProgrammes {
	// The array of products that we will create 
	NSMutableArray *programmes = [[[NSMutableArray alloc] init] autorelease];
	
	// The SQL statement that we plan on executing against the database 
	NSString *sql =	@"SELECT * from ZPROGRAMME";
	
	// The SQLite statement object that will hold our result set 
	sqlite3_stmt *statement=[DataAccess prepareSQL:sql];
	if(statement){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			Programme *programme = [[Programme alloc] init];
			[programme initWithSQL:statement];
			[programmes addObject:programme];
			[programme release];
		}
		sqlite3_finalize(statement);
	}
	return programmes;
}

+ (NSArray*) getProgrammesInDepartment:(NSString*)dept {
	// The array of products that we will create 
	NSMutableArray *programmes = [[[NSMutableArray alloc] init] autorelease];
	
	// The SQL statement that we plan on executing against the database 
	NSString *sql =	@"select ZPROGRAMME.* from ZDEPARTMENT, ZPROGRAMME WHERE ZDEPARTMENT.Z_PK=ZPROGRAMME.ZDEPARTMENT AND ZABBREVIATION='?DEPT?'";
	sql=[sql stringByReplacingOccurrencesOfString:@"?DEPT?" withString:dept];
						
	
	// The SQLite statement object that will hold our result set 
	sqlite3_stmt *statement=[DataAccess prepareSQL:sql];
	if(statement){
		while (sqlite3_step(statement) == SQLITE_ROW) {
			Programme *programme = [[Programme alloc] init];
			[programme initWithSQL:statement];
			[programmes addObject:programme];
			[programme release];
		}
		sqlite3_finalize(statement);
	}

	return programmes;
}


+ (Department*) getDepartment:(NSString*)abbreviation {
	// The array of products that we will create 
	
	// The SQL statement that we plan on executing against the database 
	NSString *sql =	@"SELECT * from ZDEPARTMENT WHERE ZABBREVIATION = '?1?'";
	sql=[sql stringByReplacingOccurrencesOfString:@"?1?" withString:abbreviation];
	
	sqlite3_stmt *statement=[DataAccess prepareSQL:sql];
	Department *department =nil;
	if(statement && sqlite3_step(statement) == SQLITE_ROW){
		department = [[[Department alloc] init] autorelease];
		[department initWithSQL:statement];
		sqlite3_finalize(statement);

	}
	return department;
}

+(sqlite3_stmt *)prepareSQL:(NSString*)sql{
	// The SQLite statement object that will hold our result set 
	sqlite3_stmt *statement;
	// Prepare the statement to compile the SQL query into byte-code 
	int sqlResult = sqlite3_prepare_v2(database, [sql UTF8String], -1, &statement, NULL);
	NSLog(@"Preparing SQL stmt:%@",sql);
	if ( sqlResult!= SQLITE_OK) { 
		NSLog(@"Problem with the database:");
		NSLog(@"can't Execute SQL: %@ \n", sql); 
		NSLog(@"%d",sqlResult);
		return nil;
	}
	return statement;
		
}

+(NSString*)getStringFrom:(sqlite3_stmt*)sql InCol:(int)col{
	char* sqlString= (char *)sqlite3_column_text(sql, col);
	NSString* ret = (sqlString) ? [NSString stringWithUTF8String:sqlString] : @"";
	ret=[ret stringByTrimmingCharactersInSet:[NSCharacterSet whitespaceAndNewlineCharacterSet]];
	return ret;

}


@end
